<?php
/**
 * author     : dwheng
 * createTime : 2015/6/29 19:43
 * description:
 */

namespace zhongkefu\helpers;


use yii\base\Object;
use Yii;
use yii\db\Exception;

class MysqlBackup extends Object
{
    public $menu = [];
    public $tables = [];
    public $fp;
    public $file_name;
    public $_path = '@runtime';
    public $back_temp_file = 'db_backup_';

    protected function getPath()
    {
//        if (isset ($this->module->path)) {
//            $this->_path = $this->module->path;
//        } else {
//            $this->_path = Yii::$app->basePath . '/_backup/';
//        }
        $this->_path = Yii::getAlias('@runtime') . '/_backup/';

        if (!file_exists($this->_path)) {
            mkdir($this->_path);
            chmod($this->_path, '777');
        }
        return $this->_path;
    }

    public function execSqlFile($sqlFile)
    {
        $message = "ok ";

        if (file_exists($sqlFile)) {
            $sqlArray = file_get_contents($sqlFile);

            $cmd = Yii::$app->db->createCommand($sqlArray);
            try {
                $cmd->execute();
            } catch (Exception $e) {
                $message = $e->getMessage();
            }

        }
        return $message;
    }

    public function getColumns($tableName)
    {
        $sql = 'SHOW CREATE TABLE ' . '`' . $tableName . '`';
        $cmd = Yii::$app->db->createCommand($sql);
        $table = $cmd->queryOne();

        $create_query = $table['Create Table'] . ';';

        $create_query = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query);
        $create_query = preg_replace('/AUTO_INCREMENT\s*=\s*([0-9])+/', '', $create_query);
        if ($this->fp) {
            $this->writeComment('TABLE `' . addslashes($tableName) . '`');
            $final = 'DROP TABLE IF EXISTS `' . addslashes($tableName) . '`;' . PHP_EOL . $create_query . PHP_EOL . PHP_EOL;
            fwrite($this->fp, $final);
        } else {
            $this->tables[$tableName]['create'] = $create_query;
            return $create_query;
        }
    }

    public function getData($tableName)
    {
		header ( "Content-type: text/html;charset=utf-8" ); 
        $sql = 'SELECT * FROM ' . '`' . $tableName . '`';
        $cmd = Yii::$app->db->createCommand($sql);
        $dataReader = $cmd->query();

        $data_string = '';

        foreach ($dataReader as $data) {
            $itemNames = array_keys($data);
            $itemNames = array_map("addslashes", $itemNames);
            $items = join('`,`', $itemNames);
            $itemValues = array_values($data);
            $itemValues = array_map("addslashes", $itemValues);
            $valueString = join("','", $itemValues);
            $valueString = "('" . $valueString . "'),";
			$values = $valueString;
            if ($values != "") {
                $data_string .= "INSERT INTO `$tableName` VALUES " . rtrim($values, ",") . ";" . PHP_EOL;
            }
        }

        if ($data_string == '')
            return null;

        if ($this->fp) {
            $this->writeComment('TABLE DATA ' . $tableName);
            $final = $data_string . PHP_EOL . PHP_EOL . PHP_EOL;
            fwrite($this->fp, $final);
        } else {
            $this->tables[$tableName]['data'] = $data_string;
            return $data_string;
        }
    }

    public function getTables($dbName = null)
    {
        $sql = 'SHOW TABLES';
        $cmd = Yii::$app->db->createCommand($sql);
        $tables = $cmd->queryColumn();
        return $tables;
    }

    public function StartBackup($addcheck = true)
    {
        $this->file_name = $this->path . $this->back_temp_file . date('Y.m.d_H.i.s') . '.sql';

        $this->fp = fopen($this->file_name, 'w+');

        if ($this->fp == null)
            return false;
        fwrite($this->fp, '-- -------------------------------------------' . PHP_EOL);
        if ($addcheck) {
            fwrite($this->fp, 'SET AUTOCOMMIT=0;' . PHP_EOL);
            fwrite($this->fp, 'START TRANSACTION;' . PHP_EOL);
            fwrite($this->fp, 'SET SQL_QUOTE_SHOW_CREATE = 1;' . PHP_EOL);
        }
        fwrite($this->fp, 'SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;' . PHP_EOL);
        fwrite($this->fp, 'SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;' . PHP_EOL);
        fwrite($this->fp, '-- -------------------------------------------' . PHP_EOL);
        $this->writeComment('START BACKUP');
        return true;
    }

    public function EndBackup($addcheck = true)
    {
        fwrite($this->fp, '-- -------------------------------------------' . PHP_EOL);
        fwrite($this->fp, 'SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;' . PHP_EOL);
        fwrite($this->fp, 'SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;' . PHP_EOL);

        if ($addcheck) {
            fwrite($this->fp, 'COMMIT;' . PHP_EOL);
        }
        fwrite($this->fp, '-- -------------------------------------------' . PHP_EOL);
        $this->writeComment('END BACKUP');
        fclose($this->fp);
        $this->fp = null;
        return $this->file_name;
    }

    public function writeComment($string)
    {
        fwrite($this->fp, '-- -------------------------------------------' . PHP_EOL);
        fwrite($this->fp, '-- ' . $string . PHP_EOL);
        fwrite($this->fp, '-- -------------------------------------------' . PHP_EOL);
    }

    /* 
     * 下载文件 
     * @param string $fileName 路径 
    */  
    public function Down($fileName) {
        header ("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
        header('Content-Description: File Transfer'); 
        header('Content-Type: application/octet-stream'); 
        header('Content-Length: ' . filesize($fileName)); 
        header('Content-Disposition: attachment; filename=' . basename($fileName));  
        readfile($fileName);  
    } 
	
	    /** 
     * 导入备份数据 
     * 说明：分卷文件格式20120516211738_all_v1.sql 
     * 参数：文件路径(必填) 
     */ 
    public function restore($sqlfile)  
    {  
		header ( "Content-type: text/html;charset=utf-8" );  
        // 检测文件是否存在  
        if(!file_exists($sqlfile))  
        {  
            exit( "文件不存在！请检查");  
        }  
        //$this->lock($this->database);  
        // 获取数据库存储位置  
        $sqlpath = pathinfo($sqlfile);
        $sqldir = $sqlpath ['dirname'];  
        if($this->_import($sqlfile)) {  
             return true;
        }else{  
             return false;
        }
 
    }  
   
    /** 
     * 将sql导入到数据库（普通导入） 
     * 
     * @param string $sqlfile 
     * @return boolean 
     */ 
    private function _import($sqlfile) {  
        // sql文件包含的sql语句数组  
        $sqls = array(); 
        $f = fopen ( $sqlfile, "rb" );  
        // 创建表缓冲变量  
        $create = '';  
        while ( ! feof ( $f ) ) {  
            // 读取每一行sql  
            $line = fgets ( $f );  
            // 如果包含'-- '等注释，或为空白行，则跳过  
            if (trim ( $line ) == '' || preg_match ( '/-{2,}/', $line, $match )) {  
                continue;  
            }  
            // 如果结尾包含';'(即为一个完整的sql语句，这里是插入语句)，并且不包含'ENGINE='(即创建表的最后一句)，  
            if (! preg_match ( '/;/', $line, $match ) || preg_match ( '/ENGINE=/', $line, $match )) {  
                // 将本次sql语句与创建表sql连接存起来  
                $create .= $line;  
                // 如果包含了创建表的最后一句  
                if (preg_match ( '/ENGINE=/', $create, $match )) {  
                    // 则将其合并到sql数组  
                    $sqls [] = $create;  
                    // 清空当前，准备下一个表的创建  
                    $create = '';  
                }  
                // 跳过本次  
                continue;  
            }  
            $sqls [] = $line;  
        }  
        fclose ( $f );  
        // 循环sql语句数组，分别执行  
        foreach ( $sqls as $sql ) {  
            str_replace ( "n", "", $sql);
			if(preg_match("/auth_item/",$sql)){
				$sql = str_replace("''",'NULL',$sql);
			}
			$command = Yii::$app->db->createCommand(trim($sql));			
            if(!$command->query()) {   
                return false;  
            }  
        }  
        return true;  
    }  
    
   
    // 锁定数据库，以免备份或导入时出错  
    private function lock($tablename, $op = "WRITE") {  
        if (mysql_query ( "lock tables " . $tablename . " " . $op ))  
            return true;  
        else 
            return false;  
    }  
   
    // 解锁  
    private function unlock() {  
        if (mysql_query ( "unlock tables" ))  
            return true;  
        else 
            return false;  
    }  

}